﻿using System;
using System.Collections.Generic;
using System.Data;
using DbView;

namespace DbView
{
	public class SqlScripter
	{
		/// <summary>
		/// functions for generating a field and literal string eg FIELD='VALUE'
		/// </summary>
		/// <param name="fi">field info for the target field eg Field Name</param>
		/// <param name="data">data associated to the field eg from a DataRow</param>
		/// <returns></returns>
		delegate string FieldLiteralScripter(FieldInfo fi, object data);

		/// <summary>
		/// functions for generating a target field and source field assignment eg TABLE1.FIELD=TABLE2.FIELD
		/// </summary>
		/// <param name="targetTableName">Table being updated</param>
		/// <param name="targetField">field info for the target field eg Field Name</param>
		/// <param name="sourceTableName">Source table</param>
		/// <param name="sourceFields">field info for the source field eg Field Name</param>
		/// <returns></returns>
		delegate string FieldSourceScripter(string targetTableName, FieldInfo targetFields, string sourceTableName, FieldInfo sourceFields);

		/// <summary>
		/// Target table schema supplied in the constructor
		/// </summary>
		public FieldInfo[] FieldDefinitions{ get; set; }
		/// <summary>
		/// Target table name supplied in the constructor
		/// </summary>
		public String TableName {get; set; }

		/// <summary>
		/// 
		/// </summary>
		/// <param name="tableName">Target table being updated in the script</param>
		/// <param name="fieldInfo">schema of the table</param>
		public SqlScripter(string tableName, FieldInfo[] fieldInfo)
		{
			TableName = tableName;
			FieldDefinitions = fieldInfo;
		}
		/// <summary>
		/// Scripts an insert statement matching fields in the target table to names in the update data row
		/// </summary>
		/// <param name="dr">data to be scripted</param>
		/// <returns>a a script insering literal data into the target table</returns>
		public string ScriptInsert(DataRow dr)
		{
			return ScriptInsert(dr, this.TableName, this.FieldDefinitions);
		}
		/// <summary>
		/// Scripts an insert statement matching fields in the target table to names in the update data row
		/// </summary>
		/// <param name="dr">data to be scripted</param>
		/// <param name="tableName">The target table name</param>
		/// <param name="fields">schema of the table</param>
		/// <returns></returns>
		public static string ScriptInsert(DataRow dr, String tableName, FieldInfo[] fields)
		{
			string template = "insert into {0} ({1}) values ({2});";
			string insertNames = MakeFieldListStringWithData(fields, dr, SafeFieldName, ", ");
			string insertValues = MakeFieldListStringWithData(fields, dr, FieldLiteral, ", ");
			
			return String.Format(template, tableName, insertNames, insertValues);
			
		}
		/// <summary>
		/// Scripts an updte statement matching fields in the target table to names in the update data row
		/// </summary>
		/// <param name="dr">data to be scripted</param>
		/// <returns>a a script updating a row in the target data with literal data</returns>
		public string ScriptUpdate(DataRow dr)
		{
			return ScriptUpdate(dr, this.TableName, this.FieldDefinitions);
		}

		/// <summary>
		/// Scripts an updte statement matching fields in the target table to names in the update data row
		/// </summary>
		/// <param name="dr">data to be scripted</param>
		/// <param name="tableName">The target table name</param>
		/// <param name="fields">schema of the table</param>
		/// <returns>a a script updating a row in the target data with literal data</returns>
		public static string ScriptUpdate(DataRow dr, String tableName, FieldInfo[] fields)
		{
			string template = "update {0} set {1} where {2};";
			string pkCompare = MakeFieldListStringWithData(fields, dr, PKCompare, ", ");
			string updateValues = MakeFieldListStringWithData(fields, dr, UpdateWithLiteral, ", ");
			// rare case. no fields to update
			if (string.IsNullOrEmpty(updateValues))
				return "-- No fields found to script an update for.";
			return String.Format(template, tableName, updateValues, pkCompare);

		}
		
		/// <summary>
		/// Scripts a conditional insert or update statement depending on whether the row exists in the target table
		/// matching fields in the target table to names in the update data row
		/// </summary>
		/// <param name="dr">data to be scripted</param>
		/// <returns>a a script updating a row in the target data with literal data</returns>
		public string ScriptUpsert(DataRow dr)
		{
			return ScriptUpsert(dr, this.TableName, this.FieldDefinitions);
		}

		/// <summary>
		/// Scripts a conditional insert or update statement depending on whether the row exists in the target table
		/// matching fields in the target table to names in the update data row
		/// </summary>
		/// <param name="dr">data to be scripted</param>
		/// <param name="tableName">The target table name</param>
		/// <param name="fields">schema of the table</param>
		/// <returns>a script updating a row in the target data with literal data</returns>
		public static string ScriptUpsert(DataRow dr, String tableName, FieldInfo[] fields)
		{
			string template = @"
if exists (select * from {0} where {2}) 
	update {0} set {1} where {2}
else
	insert into {0} ({3}) values ({4});
			";
			string insertNames = MakeFieldListStringWithData(fields, dr, SafeFieldName, ", ");
			string insertValues = MakeFieldListStringWithData(fields, dr, FieldLiteral, ", ");
			string pkCompare = MakeFieldListStringWithData(fields, dr, PKCompare, ", ");
			string updateValues = MakeFieldListStringWithData(fields, dr, UpdateWithLiteral, ", ");
			
			return String.Format(template, tableName, updateValues, pkCompare, insertNames, insertValues);
		}

		/// <summary>
		/// scripts a statement to add rows to the target table from the source table conditional 
		/// on the absence of the row in the target table
		/// </summary>
		/// <param name="targetTableName">The target table name ie. the table to be upated</param>
		/// <param name="targetFields">schema of the target table</param>
		/// <param name="sourceTableName">The source table name ie. the table containing the data to add to the target</param>
		/// <param name="sourceFields">schema of the source table</param>
		/// <returns>a SQL script</returns>
		public static string ScriptMergeAppend(String targetTableName, FieldInfo[] targetFields, string sourceTableName, FieldInfo[] sourceFields)
		{
			string template = @"
	insert into {0} ({3})
    select {4} 
	  from {1} 
	 where not exists (select * from {0} where {2})";
			string insertNames = MakeFieldListStringWithSourceTable(targetTableName, targetFields, sourceTableName, sourceFields, SafeTargetFieldName, ", ");
			string insertValues = MakeFieldListStringWithSourceTable(targetTableName, targetFields, sourceTableName, sourceFields, SafeSourceTableFieldName, ", ");
			string pkCompare = MakeFieldListStringWithSourceTable(targetTableName, targetFields, sourceTableName, sourceFields, CompareKeyField, " and ");
			
			return String.Format(template, targetTableName, sourceTableName, pkCompare, insertNames, insertValues);
		}
		/// <summary>
		/// scripts a statement to add rows to the target table from the source table conditional 
		/// on the absence of the row in the target table
		/// </summary>
		/// <param name="sourceTableName">The source table name ie. the table containing the data to add to the target</param>
		/// <param name="sourceFields">schema of the source table</param>
		/// <returns>a SQL script</returns>
		public string ScriptMergeAppend(string sourceTableName, FieldInfo[] sourceFields)
		{
			return ScriptMergeAppend(this.TableName, this.FieldDefinitions, sourceTableName, sourceFields);
		}
		
		/// <summary>
		/// scripts a statement to update rows in the target table from the source table conditional 
		/// when the row is present in the target table. ie. this function never adds any new rows to the target
		/// </summary>
		/// <param name="targetTableName">The target table name ie. the table to be upated</param>
		/// <param name="targetFields">schema of the target table</param>
		/// <param name="sourceTableName">The source table name ie. the table containing the data to update the target</param>
		/// <param name="sourceFields">schema of the source table</param>
		/// <returns>a SQL script</returns>
		public static string ScriptMergeUpdate(String targetTableName, FieldInfo[] targetFields, string sourceTableName, FieldInfo[] sourceFields)
		{
// SQLite needs this: update TEST set [NAME]=(select NAME from TEST_STAGE where TEST_STAGE.ID=TEST .ID)

			string template = @"
	update {0} set {3}
	  from {1} 
	 where {2}";
			string insertNames = MakeFieldListStringWithSourceTable(targetTableName, targetFields, sourceTableName, sourceFields, SafeTargetFieldName, ", ");
			string updateValues = MakeFieldListStringWithSourceTable(targetTableName, targetFields, sourceTableName, sourceFields, UpdateFromSource, ", ");
			string pkCompare = MakeFieldListStringWithSourceTable(targetTableName, targetFields, sourceTableName, sourceFields, CompareKeyField, " and ");
			// deliberately allow a no PK fields to generate invalid SQL.
			if (string.IsNullOrEmpty(updateValues))
				return "-- No fields found to script an update for.";
			return String.Format(template, targetTableName, sourceTableName, pkCompare, updateValues);
		}
		
		/// <summary>
		/// scripts a statement to update rows in the target table from the source table conditional 
		/// when the row is present in the target table. ie. this function never adds any new rows to the target
		/// </summary>
		/// <param name="sourceTableName">The source table name ie. the table containing the data to update the target</param>
		/// <param name="sourceFields">schema of the source table</param>
		/// <returns>a SQL script</returns>
		public string ScriptMergeUpdate(string sourceTableName, FieldInfo[] sourceFields)
		{
			return ScriptMergeUpdate(this.TableName, this.FieldDefinitions, sourceTableName, sourceFields);
		}

		/// <summary>
		/// scripts a statement to update rows in the target table from the source table conditional 
		/// when the row is present in the target table. ie. this function never adds any new rows to the target.
		/// This is a special method for SQLite only as this database requires a different syntax
		/// </summary>
		/// <param name="targetTableName">The target table name ie. the table to be upated</param>
		/// <param name="targetFields">schema of the target table</param>
		/// <param name="sourceTableName">The source table name ie. the table containing the data to update the target</param>
		/// <param name="sourceFields">schema of the source table</param>
		/// <returns>a SQL script suitable for SQLite</returns>
		public static string ScriptMergeUpdateSqlite(String targetTableName, FieldInfo[] targetFields, string sourceTableName, FieldInfo[] sourceFields)
		{
			// SQLite needs this: update TEST set [NAME]=(select NAME from TEST_STAGE where TEST_STAGE.ID=TEST .ID)
			string template = @"
	update {0} set {1}
	 where exists (select * from {2} where {3})";

			string insertNames = MakeFieldListStringWithSourceTable(targetTableName, targetFields, sourceTableName, sourceFields, SafeTargetFieldName, ", ");
			string updateValues = MakeFieldListStringWithSourceTable(targetTableName, targetFields, sourceTableName, sourceFields, UpdateFromSqliteSubquery, ",\n");
			string pkCompare = MakeFieldListStringWithSourceTable(targetTableName, targetFields, sourceTableName, sourceFields, CompareKeyField, " and ");
			updateValues = updateValues.Replace("%KEY%", pkCompare);
			// deliberately allow a no PK fields to generate invalid SQL.
			if (string.IsNullOrEmpty(updateValues))
				return "-- No fields found to script an update for.";
			return String.Format(template, targetTableName,  updateValues, sourceTableName, pkCompare);
		}
		/// <summary>
		/// scripts a statement to update rows in the target table from the source table conditional 
		/// when the row is present in the target table. ie. this function never adds any new rows to the target.
		/// This is a special method for SQLite only as this database requires a different syntax
		/// </summary>
		/// <param name="sourceTableName">The source table name ie. the table containing the data to update the target</param>
		/// <param name="sourceFields">schema of the source table</param>
		/// <returns>a SQL script</returns>
		public string ScriptMergeUpdateSqlite(string sourceTableName, FieldInfo[] sourceFields)
		{
			return ScriptMergeUpdateSqlite(this.TableName, this.FieldDefinitions, sourceTableName, sourceFields);
		}

		// Additional params as the method is shared between 2 api versions.
		/// <summary>
		/// Perform a check on the field configurations an produce warning and error messages
		/// </summary>
		/// <param name="targetTableName">The target table name ie. the table to be upated</param>
		/// <param name="targetFields">schema of the target table</param>
		/// <param name="dr">A DataRow allowing to the columns of the source data table. May be null if sourceFields is not null.</param>
		/// <param name="sourceTableName">The source table name included for error messages. May be null if dr is non-null</param>
		/// <param name="sourceFields">schema of the source table. May be null if dr is non-null</param>
		/// <returns>A blank string if there is no problem otherwise a warning or error message.</returns>
		public static string KeyCheck(String targetTableName, FieldInfo[] targetFields, DataRow dr, string sourceTableName, FieldInfo[] sourceFields)
		{
			string message = ""; // ie. OK
			String srcTableName = (dr == null) ? sourceTableName 
									: dr.Table.TableName;
			bool targetHasPK = false;
			bool noNonPKMatches = true;
			foreach (FieldInfo fi in targetFields)
			{
				bool match = (dr == null) ? (fi.Match(sourceFields) != -1) 
											: (dr[fi.Name] != null);
				if (fi.IsPKey)
				{
					targetHasPK = true;
					if (!match)
						message += string.Format("ERROR[-1]: Key {1} from {0} not found in fields for {2}\r\n", targetTableName, fi.Name, srcTableName);
				}
				else
				{
					if (!fi.IsCalc)
						noNonPKMatches = false;
				}
			}
			if (!targetHasPK)
				message += string.Format("WARNING[1]: Table {0} does not have a PK field defined\r\n", targetTableName);
			if (!noNonPKMatches)
				message += string.Format("WARNING[1]: Field list of Table {0} has no non-PK matches with {0}. Update scripts will fail.\r\n", targetTableName, srcTableName);

			return message;
		}
		/// <summary>
		/// Perform a check on the field configurations an produce warning and error messages
		/// </summary>
		/// <param name="sourceTableName">The source table name included for error messages. May be null if dr is non-null</param>
		/// <param name="sourceFields">schema of the source table. May be null if dr is non-null</param>
		/// <returns>A blank string if there is no problem otherwise a warning or error message.</returns>
		public string KeyCheck(string sourceTableName, FieldInfo[] sourceFields)
		{
			return KeyCheck(this.TableName, this.FieldDefinitions, null, sourceTableName, sourceFields);
		}

		/// <summary>
		/// Perform a check on the field configurations an produce warning and error messages
		/// </summary>
		/// <param name="dr">A DataRow allowing to the columns of the source data table. May be null if sourceFields is not null.</param>
		/// <returns>A blank string if there is no problem otherwise a warning or error message.</returns>
		public string KeyCheck(DataRow dr)
		{
			return KeyCheck(this.TableName, this.FieldDefinitions, dr, null, null);
		}


		/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
		// Field list builder and delegates

		// Iterate field info, match to the data, if a match invoke a callback to prepare a fragment of script
		//static List<string> MakeFieldListWithData(FieldInfo[] fields, DataRow dr, Func<FieldInfo, object, string> builder)
		static List<string> MakeFieldListWithData(FieldInfo[] fields, DataRow dr, FieldLiteralScripter builder)
		{
			// the return
			List<string> target = new List<string>();
			// iterate the target table schema as described in the fieldInfo list
			foreach (FieldInfo fi in fields)
			{
				// attempt a match on the data. If the item is null assume to be a non-match.
				// genuine database null data should be the System.DbNull type
				object dataItem = dr[fi.Name];
				if (dataItem != null) // match is found
				{
					// invoke the callback
					string element = builder(fi, dataItem);
					// if the callback returns nothing then no new list item is added
					// (so the list can filter to include/exclude Key fields for update statements)
					if (!String.IsNullOrEmpty(element))
						target.Add(element);
				}
			}
			return target;
		}
		// Helper string return version of the above
		//static string MakeFieldListStringWithData(FieldInfo[] fields, DataRow dr, Func<FieldInfo, object, string> builder, string separator)
		static string MakeFieldListStringWithData(FieldInfo[] fields, DataRow dr, FieldLiteralScripter builder, string separator)
		{
			return string.Join(separator, MakeFieldListWithData(fields, dr, builder).ToArray());
		}

		// Delegates for MakeFieldList
		static string SafeFieldName(FieldInfo fi, object data)
		{
			if (fi.IsCalc) return ""; // not calculated
			return fi.SafeName;
		}

		static string FieldLiteral(FieldInfo fi, object data)
		{
			if (fi.IsCalc) return ""; // not calculated
			return MakeLiteral(fi, data);
		}

		static string PKCompare(FieldInfo fi, object data)
		{
			if (!fi.IsPKey) return ""; // only if PK
			
			string stringLiteral = MakeLiteral(fi, data);
			return string.Format("{0}={1}", fi.SafeName, stringLiteral);
		}

		static string UpdateWithLiteral(FieldInfo fi, object data)
		{
			if (fi.IsPKey) return ""; // Not if PK
			if (fi.IsCalc) return ""; // not calculated
			string stringLiteral = MakeLiteral(fi, data);
			return string.Format("{0}={1}", fi.SafeName, stringLiteral);
		}

		// Iterate target table field info, match to the source fieldInfo, if a match invoke a callback to prepare a fragment of script 
		// using data from each schema
		static List<string> MakeFieldListWithSourceTable(string targetTableName, FieldInfo[] targetFields, 
														string sourceTableName, FieldInfo[] sourceFields, 
														FieldSourceScripter builder)
//														Func<string, FieldInfo, string, FieldInfo, string> builder)
		{
			// the return
			List<string> target = new List<string>();
			// iterate the target table schema as described in the fieldInfo list
			foreach (FieldInfo fi in targetFields)
			{
				// attempt a match on the source schema field
				int sourceIdx = fi.Match(sourceFields);
				if (sourceIdx != -1)
				{
					// invoke the callback
					string element = builder(targetTableName, fi, sourceTableName, sourceFields[sourceIdx]);
					// if the callback returns nothing then no new list item is added
					// (so the list can filter to include/exclude Key fields for update statements)
					if (!String.IsNullOrEmpty(element))
						target.Add(element);
				}
			}
			return target;
		}
		
		// Helper string return version of the above
		static string MakeFieldListStringWithSourceTable(string targetTableName, FieldInfo[] targetFields, 
															string sourceTableName, FieldInfo[] sourceFields, 
															FieldSourceScripter builder, string separator)
//															Func<string, FieldInfo, string, FieldInfo, string> builder, string separator)
		{
			return string.Join(separator, MakeFieldListWithSourceTable(targetTableName, targetFields, sourceTableName, sourceFields, builder).ToArray());
		}
		
		static string SafeTargetFieldName(string targetTableName, FieldInfo targetField, string sourceTableName, FieldInfo sourceField)
		{
			if (targetField.IsCalc) return ""; // not calculated
			return targetField.SafeName;
		}

		static string SafeSourceTableFieldName(string targetTableName, FieldInfo targetField, string sourceTableName, FieldInfo sourceField)
		{
			if (targetField.IsCalc) return ""; // not calculated
			return string.Format("{0}.{1}",sourceTableName, sourceField.SafeName);
		}
		
		static string UpdateFromSource(string targetTableName, FieldInfo targetField, string sourceTableName, FieldInfo sourceField)
		{
			if (targetField.IsPKey) return ""; // Not if PK
			if (targetField.IsCalc) return ""; // not calculated
			return string.Format("{1}={0}.{2}", sourceTableName, targetField.SafeName, sourceField.SafeName);
		}

		
		static string CompareKeyField(string targetTableName, FieldInfo targetField, string sourceTableName, FieldInfo sourceField)
		{
			if (!targetField.IsPKey) return ""; // Only if PK
			return string.Format("{0}.{2}={1}.{3}", targetTableName, sourceTableName, targetField.SafeName, sourceField.SafeName);
		}
		
		//[NAME]=(select NAME from TEST_STAGE where TEST_STAGE.ID=TEST .ID)
		static string UpdateFromSqliteSubquery(string targetTableName, FieldInfo targetField, string sourceTableName, FieldInfo sourceField)
		{
			if (targetField.IsPKey) return ""; // Not if PK
			if (targetField.IsCalc) return ""; // not calculated
			// add a placemarker to be resolved by the Key comp
			return string.Format("{1}=(select {2} from {0} where {3})", sourceTableName, targetField.SafeName, sourceField.SafeName, "%KEY%");
		}
		
		
		////////////////////////////////////////////////////////////////////////////////////////
		// Helpers

		private static string MakeLiteral(FieldInfo fi, object dataItem)
		{
			string stringValue = dataItem.ToString();
			if (stringValue.Length == 0)
				stringValue = "NULL";
			else
			{
				switch (fi.Type)
				{
					case "D":
						stringValue = "'" + MakeDateLiteral(dataItem) + "'";
						break;
					case "B":
						stringValue = MakeBitInsertField(dataItem);
						break;
					case "C":
						{
							String prefix = (fi.IsUnicode) ? "N" : "";
							stringValue = prefix + "'" + stringValue.Replace("\'", "\'\'") + "'";
							break;
						}
					default:
						break;
				}
			}
			return stringValue;
		}

		static String MakeDateLiteral(object o)
		{
			// assume 
			DateTime dt = (DateTime)o;
			String s = dt.ToString("yyyyMMdd HH:mm:ss");
			return s;
		}

		static String MakeBitInsertField(object o)
		{
			// assume 
			if (o.GetType() == typeof(bool))
				return ((bool)o) ? "1" : "0";
			if (o.GetType() == typeof(int))
				return ((int)o != 0) ? "1" : "0";
			string data = o.ToString();
			return (data.ToUpper() == "TRUE" ? "1" :
									 data.ToUpper() == "FALSE" ? "0" :
									 data.ToUpper() == "YES" ? "1" :
									 data.ToUpper() == "NO" ? "0" :
									 data.ToUpper() == "Y" ? "1" :
									 data.ToUpper() == "N" ? "0" : "0");
		}

	}

	
}
